﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using ForkCarRelationManager.db;
namespace ForkCarRelationManager.Business
{
    class MaintorBI
    {
        DataTable maintroTable;
        DataPaging MaintorPage;
        DataSet MaintorDataSet;
     
        dbOperate dbo = new dbOperate();
        
        public DataPaging GetMaintenpage(int pagecount)
        {
            MaintorPage = new DataPaging("MaintorInfor m,ScompanyInfor s", pagecount, "(select sum(1) from (select p.ID from MaintorInfor p,ScompanyInfor s where s.ID =p.ScompanyId) t  where t.ID<=m.ID )  as 序号,m.MaintorName,m.MaintorAge,m.MaintorSex,m.MaintorNum,m.PhoneNumber,m.MaintorPartment,m.MaintorPosition,s.ScompanyName,iif(m.ID=0,'查看',iif(m.ID>0,'查看')) as 查看,m.ID as ID ", "s.ID =m.ScompanyId ", "m");
            return MaintorPage;
        }

        /*
      * 获取总数据量
      * */
        public int GetDatacount(DataPaging datapage)
        {
            int datacount = 0;
            if (datapage != null)
            {
                datacount = datapage.Getcount();
            }
            return datacount;
        }
        /*
     * 获取职工信息
     * */
        public DataTable Getmaintorinfor(int maintorid)
        {
            String sqlMaintor = "select  m.MaintorName,m.MaintorAge,m.MaintorSex,m.MaintorNum,m.PhoneNumber,s.ScompanyName,m.remark,m.MaintorPartment,m.MaintorPosition from MaintorInfor m,ScompanyInfor s   where s.ID =m.ScompanyId and m.ID=" + maintorid;
            DataTable MaintorTable = dbo.GetDate(sqlMaintor);
            if (MaintorTable == null)
            {
                MaintorTable = new DataTable();
                MaintorTable.Columns.Add("ID");
                MaintorTable.Columns.Add("MaintorName");
                MaintorTable.Rows.Add("", "");
            }
            return MaintorTable;
        }
        /*
    * 更改职工信息
    * */
        public bool Updatemaintor(string MaintorName, int MaintorAge, int ScompanyId,string MaintorNum, string PhoneNumber, string Remark, string MaintorSex, int maintorid, String partment, String position)
        {
            String sql = "update MaintorInfor set MaintorName='" + MaintorName + "',MaintorAge=" + MaintorAge + ",PhoneNumber='" + PhoneNumber + "',Remark='" + Remark + "',MaintorSex='" + MaintorSex + "',MaintorPartment='" + partment + "',MaintorPosition='" + position + "',ScompanyId=" + ScompanyId + ",MaintorNum='" + MaintorNum + "' where ID=" + maintorid;
            return dbo.UpdateAndInsert(sql);
        }
        /*
* 添加职工信息
* */
        public bool Addmaintor(string MaintorName, int MaintorAge, int ScompanyId, string MaintorSex, string MaintorNum, string PhoneNumber, string Remark,String partment,String position)
        {
            bool ifaddsucs = dbo.UpdateAndInsert("insert into MaintorInfor (MaintorName,MaintorAge,ScompanyId,MaintorSex,MaintorNum,PhoneNumber,Remark,MaintorPartment,MaintorPosition) values ('" + MaintorName + "'," + MaintorAge + "," + ScompanyId + ",'" + MaintorSex + "','" + MaintorNum + "','" + PhoneNumber + "','" + Remark +"','"+partment+"','"+position+ "')");
            return ifaddsucs;
        }
        /*
       * 获取公司数据
       * */
        public DataTable Getallscompany()
        {
            String sqlCompany = "select s.ID,s.ScompanyName from ScompanyInfor as s";
            DataTable companyTable = dbo.GetDate(sqlCompany);
            if (companyTable == null)
            {
                companyTable = new DataTable();
                companyTable.Columns.Add("ID");
                companyTable.Columns.Add("ScompanyName");
                companyTable.Rows.Add("", "");
            }
            return companyTable;
        }
        /*
         *快速搜索员工 
         * */
        public DataPaging Searchmaintor(int pagecount, string condition, string companyid)
        {
         
                string conditionsql = "";
                string filedconditionsql = "";
                if (condition.Trim() != "" && condition != null)
                {
                    conditionsql += " and (m.MaintorName Like '%" + condition + "%' ";
                    filedconditionsql += " and (p.MaintorName Like '%" + condition + "%' ";
                    conditionsql += " or  m.MaintorNum Like '%" + condition + "%' ";
                    filedconditionsql += " or  p.MaintorNum Like '%" + condition + "%' ";
                    conditionsql += " or m.PhoneNumber Like '%" + condition +  "%') ";
                    filedconditionsql += " or p.PhoneNumber Like '%" + condition +  "%') ";
                    

                }
                if(companyid.Trim()!="0")
                {
                conditionsql += " and m.ScompanyID=" + companyid + " ";
                filedconditionsql += " and p.ScompanyID=" + companyid + " ";
                }



                MaintorPage = new DataPaging("MaintorInfor m,ScompanyInfor s ", pagecount, "(select sum(1) from (select p.ID from MaintorInfor p,ScompanyInfor s where s.ID =p.ScompanyId" + filedconditionsql + " order by p.id ) t where t.ID<=m.ID )   as 序号,m.MaintorName,m.MaintorAge,m.MaintorSex,m.MaintorNum,m.PhoneNumber,m.MaintorPartment,m.MaintorPosition,s.ScompanyName,iif(m.ID=0,'查看',iif(m.ID>0,'查看')) as 查看,m.ID as ID ", " s.ID =m.ScompanyId " + conditionsql, "m");


                return MaintorPage;
        
        }

        //获取某公司的员工
        public DataTable findMaintorInCompany(int companyId)
        {
            String sql = "select * from MaintorInfor where ScompanyId = " + companyId;
           return dbo.GetDate(sql);
        }
        
        //获取客户所属分公司的员工
        public DataTable findMaintorByClientId(int clientId)
        {
            String sql = "SELECT MaintorInfor.MaintorName,MaintorInfor.ID from ClientInfor right join MaintorInfor on ClientInfor.ScompanyId = MaintorInfor.ScompanyId where ClientInfor.ID = " + clientId;
            return dbo.GetDate(sql);
        }
    }
}
